package com.gym.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;

import com.gym.model.entity.CoursePlanning;
import com.gym.model.entity.Day;
import com.gym.model.entity.WeekDayForPlan;
import com.gym.model.entity.WeekPlanning;
import com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException;

public class DAOCoursePlanning extends DAO {

	public DAOCoursePlanning() throws ConnessioneException {
		super();
	}
	
	
	public int insert(CoursePlanning cp) throws MetodoDaoException, ViolazioneIntegritaException{
		
		String query = "INSERT INTO courseplanning(idcourse, day, hour, item) values(?, ?, ?, ?)";
		
		try{
			pst = con.prepareStatement(query);
			//sostituisce i marcatori ?:
			pst.setInt(1, cp.getIdcourse());
			pst.setString(2, cp.getDay().name());
			pst.setString(3, cp.getHour());
			pst.setShort(4, cp.getItem());
			
			return pst.executeUpdate();
	
		} catch (MySQLIntegrityConstraintViolationException e) {

			if(e.getErrorCode()==1062) //UNIQUE INDEX duplicato
				throw new ViolazioneIntegritaException
				("ERRORE INSERT COURSEPLANNING: UNIQUE INDEX "
				+"(day="+cp.getDay().name()+" , hour="+cp.getHour()+" , item="+cp.getItem()+") duplicato!"
				+"  |  message: "+e.getMessage());
			else
				throw new ViolazioneIntegritaException("ERRORE INSERT COURSEPLANNING: altra violazione R.I.: "+e.getMessage()
													  +" - ErrorCode="+e.getErrorCode());

		} catch (SQLException e) {
			throw new MetodoDaoException("ERRORE INSERT COURSEPLANNING: "+e.getMessage()+
										 " - ErrorCode="+e.getErrorCode()+"  |  QUERY: "+pst);
		}finally{
			try {pst.close();} catch(SQLException e){ throw new MetodoDaoException("ERRORE CHIUSURA PREPARED-STATEMENT pst.close();"); }
		}
	}
	
	
	public ArrayList<WeekPlanning> selectWeekPlanning() throws MetodoDaoException, DatoNonTrovatoException {

		//NB: la PROIEZIONE restituise 51 colonne: 7x7 + 2
		String query =  
			"SELECT"  //questo blocco proietta 49 colonne: 7 righe x 7 campi
		+	"     monday.day,   monday.id,   monday.lifespan,   monday.activity,   monday.idtrainer,   monday.trainerfirstname,   monday.trainerlastname "
		+	"  , tuesday.day,  tuesday.id,  tuesday.lifespan,  tuesday.activity,  tuesday.idtrainer,  tuesday.trainerfirstname,  tuesday.trainerlastname "
		+	", wednesday.day,wednesday.id,wednesday.lifespan,wednesday.activity,wednesday.idtrainer,wednesday.trainerfirstname,wednesday.trainerlastname "
		+	" , thursday.day, thursday.id, thursday.lifespan, thursday.activity, thursday.idtrainer, thursday.trainerfirstname, thursday.trainerlastname "
		+	"   , friday.day,   friday.id,   friday.lifespan,   friday.activity,   friday.idtrainer,   friday.trainerfirstname,   friday.trainerlastname "
		+	" , saturday.day, saturday.id, saturday.lifespan, saturday.activity, saturday.idtrainer, saturday.trainerfirstname, saturday.trainerlastname "
		+	"   , sunday.day,   sunday.id,   sunday.lifespan,   sunday.activity,   sunday.idtrainer,   sunday.trainerfirstname,   sunday.trainerlastname "

		+   " , week.hour, week.item "  //50a e 51a colonna

		+	"FROM "
		+	"	(SELECT p.day, p.hour, p.item "
		+	"	FROM courseplanning p "
		+	"	WHERE p.day = 'week') week "
		+	"		LEFT JOIN "
		+	"	(SELECT p.day, p.hour, p.item, c.id, c.lifespan, c.activity, c.idtrainer, t.trainerfirstname, t.trainerlastname "
		+	"	FROM course c, trainer t, courseplanning p "
		+	"	WHERE c.id = p.idcourse AND c.idtrainer = t.id AND p.day = 'MONDAY') monday  ON week.hour = monday.hour AND week.item = monday.item "
		+	"		LEFT JOIN "
		+	"	(SELECT p.day, p.hour, p.item, c.id, c.lifespan, c.activity, c.idtrainer, t.trainerfirstname, t.trainerlastname "
		+	"	FROM course c, trainer t, courseplanning p "
		+	"	WHERE c.id = p.idcourse AND c.idtrainer = t.id AND p.day = 'TUESDAY') tuesday  ON week.hour = tuesday.hour AND week.item = tuesday.item "
		+	"		LEFT JOIN "
		+	"	(SELECT p.day, p.hour, p.item, c.id, c.lifespan, c.activity, c.idtrainer, t.trainerfirstname, t.trainerlastname "
		+	"	FROM course c, trainer t, courseplanning p "
		+	"	WHERE c.id = p.idcourse AND c.idtrainer = t.id AND p.day = 'WEDNESDAY') wednesday  ON week.hour = wednesday.hour AND week.item = wednesday.item "
		+	"		LEFT JOIN "
		+	"	(SELECT p.day, p.hour, p.item, c.id, c.lifespan, c.activity, c.idtrainer, t.trainerfirstname, t.trainerlastname "
		+	"	FROM course c, trainer t, courseplanning p "
		+	"	WHERE c.id = p.idcourse AND c.idtrainer = t.id AND p.day = 'THURSDAY') thursday  ON week.hour = thursday.hour AND week.item = thursday.item "
		+	"		LEFT JOIN "
		+	"	(SELECT p.day, p.hour, p.item, c.id, c.lifespan, c.activity, c.idtrainer, t.trainerfirstname, t.trainerlastname "
		+	"	FROM course c, trainer t, courseplanning p "
		+	"	WHERE c.id = p.idcourse AND c.idtrainer = t.id AND p.day = 'FRIDAY') friday  ON week.hour = friday.hour AND week.item = friday.item "
		+	"		LEFT JOIN "
		+	"	(SELECT p.day, p.hour, p.item, c.id, c.lifespan, c.activity, c.idtrainer, t.trainerfirstname, t.trainerlastname "
		+	"	FROM course c, trainer t, courseplanning p "
		+	"	WHERE c.id = p.idcourse AND c.idtrainer = t.id AND p.day = 'SATURDAY') saturday  ON week.hour = saturday.hour AND week.item = saturday.item "
		+	"		LEFT JOIN "
		+	"	(SELECT p.day, p.hour, p.item, c.id, c.lifespan, c.activity, c.idtrainer, t.trainerfirstname, t.trainerlastname "
		+	"	FROM course c, trainer t, courseplanning p "
		+	"	WHERE c.id = p.idcourse AND c.idtrainer = t.id AND p.day = 'SUNDAY') sunday  ON week.hour = sunday.hour AND week.item = sunday.item "

		+	"WHERE NOT (monday.id IS NULL "
		+	"    AND   tuesday.id IS NULL "
		+	"    AND wednesday.id IS NULL "
		+	"    AND  thursday.id IS NULL "
		+	"    AND    friday.id IS NULL "
		+	"    AND  saturday.id IS NULL "
		+	"    AND    sunday.id IS NULL) "
			    
		+	"ORDER BY week.hour, week.item";  //ordinamento finale necessario!
				
		
		ArrayList<WeekPlanning> al = new ArrayList<WeekPlanning>();
		try {
			stm = con.createStatement();
			
			res = stm.executeQuery(query); //esegue la mega-query!
		
			while(res.next()) { 
				al.add( componiWeekPlanning() );
 			}
			if(al.size()==0)
				throw new DatoNonTrovatoException("Attenzione tabella COURSEPLANNING vuota!");
			else
				return al;
				
		} catch (SQLException e) {
			throw new MetodoDaoException("ERRORE SELECT COURSEPLANNING join COURSE join TRAINER"
										+"  |  message: "+e.getMessage()+" - ErrorCode="+e.getErrorCode());
		} finally {
			try {stm.close();} catch(SQLException e){ throw new MetodoDaoException("ERRORE CHIUSURA STATEMENT stm.close();"); }
		}
	}


	public int delete(CoursePlanning cp) throws MetodoDaoException, DatoNonTrovatoException {
		
		String query = "DELETE FROM COURSEPLANNING "
				  	  +"WHERE idcourse=? AND day=? AND hour=? AND item=?";
		try {
			pst = con.prepareStatement(query);
			//sostituisce i marcatori ?:
			pst.setInt(1, cp.getIdcourse());
			pst.setString(2, cp.getDay().name());
			pst.setString(3, cp.getHour());
			pst.setShort(4, cp.getItem());
			
			return pst.executeUpdate(); //esegue la query di DELETE
			
		} catch (SQLException e) {
			throw new MetodoDaoException
			("ERRORE DELETE COURSEPLANNING per le colonne di ricerca: "
			+"idcourse="+cp.getIdcourse()+" , day="+cp.getDay().name()+" , hour="+cp.getHour()+" , item="+cp.getItem()
			+"  |  message: "+e.getMessage()+" - ErrorCode="+e.getErrorCode());
		} finally {
			try {pst.close();} catch(SQLException e){ throw new MetodoDaoException("ERRORE CHIUSURA PREPARED-STATEMENT pst.close();"); }
		}
	}	

	
	//METODI DI SERVIZIO: tutti private!
	private CoursePlanning componiCoursePlanning() throws SQLException{
		
		return new CoursePlanning(res.getInt("idcourse")
					, Day.valueOf(res.getString("day"))
								, res.getString("hour")
								, res.getByte("item"));		
	}
	
	
	private WeekPlanning componiWeekPlanning() throws SQLException {

		ArrayList<WeekDayForPlan> dayForPlan = new ArrayList<WeekDayForPlan>();
		
		int col = 0;
		for(short i=0; i < 7 ; i++){  //ciclo x popolare l'ArrayList di 7 elementi (giorni della settimana)
			dayForPlan.add(componyWeekDayForPlan(col));
			col = col + 7;
		}
		
		//ritorna: 50a colonna, 51a colonna e le prime 49 colonne incapsulate nell'oggetto dayForPlan:
		return new WeekPlanning(res.getString(50), res.getByte(51), dayForPlan);
	}


	private WeekDayForPlan componyWeekDayForPlan(int col) throws SQLException {

		return new WeekDayForPlan(res.getString(col+1)==null ? null : Day.valueOf(res.getString(col+1))	//day
								, res.getString(col+2)==null ? null :             res.getInt(col+2)		//idcourse
								, res.getString(col+3)==null ? null :             res.getShort(col+3)	//lifespan
								, res.getString(col+4)													//activity
								, res.getString(col+5)==null ? null :             res.getInt(col+5)		//idtrainer
								, res.getString(col+6)													//trainerfirstname
								, res.getString(col+7) );												//trainerlastname
	}
	
	
}